﻿--获取职级异动员工信息
CREATE proc [dbo].[proc_Employee_CompayMove_GetPerson]
	@eid int
as


	select

		Id,
		(select ENumber from Employee E where EId=E.Id) as ENumber,
		(select ETempNumber from Employee E where EId=E.Id) as ETempNumber,
		EId,
		(select EName from Employee E where EId=E.Id) as EName,
		(select Sex from Employee E where EId=E.Id) as Sex,
		(select IDCard from Employee E where EId=E.Id) as IDCard,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=ACompanyId))) as  AManagementarea,
		(select CompanyName From Managementarea Where Id=((select Mid from Company where id=BCompanyId))) as  BManagementarea,
		(select CompanyName from Company where id=(select Acompanyid from employee E where Id=@eid)) as ACompanyName,
		(select CompanyName from Company where id=(select Bcompanyid from employee E where Id=@eid)) as BCompanyName,
		(select DName from Department where id=(select Adepartmentid from employee E where Id=@eid)) as ADepartmentName,
		(select DName from Department where id=(select Bdepartmentid from employee E where Id=@eid)) as BDepartmentName,
		(select EName from Employee E where Employee_CompanyMove.AuditPerson=E.UserName) as AuditPersonName,
		(select PostName from Post P where id=(select PostId from employee E where Employee_CompanyMove.EId=E.Id)) as PostName,
		(select Title from Grade G where id=(select GradeId from employee E where Employee_CompanyMove.EId=E.Id)) as GradeName,
		AZhiWu,
		BZhiWu,
		Reason,
		ISNULL((select ename from employee WHERE UserName= Proposer),Proposer) AS Proposer,
		ApplionTime,
		Audit,
		Memo,
		ISNULL((select ename from employee WHERE UserName= AuditPerson),AuditPerson) AS AuditPerson,
		AuditTime,
		ISNULL(BPid,0) AS BPid,
		(Select p.Title From Province p  where p.Id=BPid) as BProvince,
		ISNULL(BCid,0) AS BCid,
		ISNULL((Select c.Title From city c  where c.Id=BCid),'') as BCity,
		ISNULL(APid,0) AS APid,
		ISNULL((Select p.Title From Province p  where p.Id=APid),'') as AProvince,
		ISNULL(ACid,0) AS ACid,
		ISNULL((Select c.Title From city c  where c.Id=ACid),'') as ACity,
		ISNULL(Execdate,'1900-01-01') AS Execdate,
		ISNULL((select E.XZJB FROM employee E where E.id=Employee_CompanyMove.EId),'') AS XZJB


	from Employee_CompanyMove
	where eid=@eid and  ACompanyId is NOT NULL AND Audit = 1